import pymysql
from .base import now,file_load,file_save
from .mysql_config import MYSQL_CONFIG


def get_connection(config=MYSQL_CONFIG):
    mysql_connect = pymysql.connect(host=config.host, port=config.port, db=config.db, user=config.user, password=config.password)
    return mysql_connect

class Db:
    def __init__(self, mysql_config = MYSQL_CONFIG):
        self.config = mysql_config

    def __enter__(self):
        connect = get_connection(config=self.config)
        #下面加参数返回字典，不加参数返回元组
        cursor = connect.cursor(pymysql.cursors.DictCursor)
        # cursor = connect.cursor()
        connect.autocommit = False
        self.connect = connect
        self.cursor = cursor
        self.connect = connect
        return self

    #必须带四个参数，否则报错
    def __exit__(self,exc_type, exc_val, exc_tb):
        self.connect.commit()
        self.cursor.close()
        self.connect.close()

    def cursor(self):
        return self.cursor

class DbL(Db):
    def __enter__(self):
        connect = get_connection(config=self.config)
        cursor = connect.cursor()
        connect.autocommit = False
        self.connect = connect
        self.cursor = cursor
        self.connect = connect
        return self
    
def get_mysql_data(sql):
    '''返回字典'''
    with Db() as db:
        try:
            db.cursor.execute(sql)
            result = db.cursor.fetchall()
            # title = [field[0] for field in db.cursor.description]
        except Exception as e:
            print(e)
            db.connect.rollback()
            return
        return result
    
def get_mysql_data_l(sql):
    '''
    返回列表
    title,
    datas
    '''
    with DbL() as db:
        try:
            db.cursor.execute(sql)
            result = db.cursor.fetchall()
            title = [field[0] for field in db.cursor.description]
        except Exception as e:
            print(e)
            db.connect.rollback()
            return
        return [title,result]
    
def get_table_title(tablename):
    sql = f'select * from {tablename}'
    with Db() as db:
        try:
            db.cursor.execute(sql)
            # result = db.cursor.fetchall()
            title = [field[0] for field in db.cursor.description]
        except Exception as e:
            print(e)
            db.connect.rollback()
            return
        return title
    
def post_mysql(*sqls):    
    with Db() as db:
        for sql in sqls:
            try:
                db.cursor.execute(sql)
            except Exception as e:
                db.connect.rollback()
                print(e)
                
def replace_mysql(tablename,datas):
    print(f'{now()} 共有{len(datas)}条数据')
    with Db() as db:
        for data in datas:
            replace_sql = f'''
                replace INTO {tablename} ({','.join([f'`{item}`' for item in data.keys() if data.get(item)])}) 
                VALUES ({','.join([f'"{item}"' for item in data.values() if item])})
            '''
            try:
                db.cursor.execute(replace_sql)
            except Exception as e:
                print(e)
                print(replace_sql)
                replace_sql = f'''
                    replace INTO {tablename} ({','.join([f'`{item}`' for item in data.keys() if data.get(item)])}) 
                    VALUES ({','.join([f"'{item}'" for item in data.values() if item])})
                '''
                try:
                    print(replace_sql)
                    db.cursor.execute(replace_sql)
                except Exception as e:
                    db.connect.rollback()
                    print(e)
                    
                    
def database_dump(datasname,filename):
    '''保存数据库，包括建表语句，及表数据'''
    datas = {}
    tables = get_mysql_data('show tables')
    tables = [item.get(f'Tables_in_{datasname}') for item in tables]
    # print(tables)
    create_sql = []
    table_datas =  {}
    for table in tables:
        sqls = get_mysql_data(f'show create table {table}')
        if sqls[0].get('Create Table'):
            print(sqls[0].get('Create Table').replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS'))
            print()
            create_sql.append(sqls[0].get('Create Table').replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS'))
            table_data = get_mysql_data(f"select * from {table}")
            table_datas[table] = table_data
    datas['create_sqls'] = create_sql
    datas['table_datas'] = table_datas
    file_save(filename,datas)

def database_load(datasname):
    '''恢复数据库，不含建库语名'''
    datas = file_load(datasname)
    sqls = datas.get('create_sqls')
    post_mysql(*sqls)
    for tablename,value in datas.get('table_datas').items():
        print(tablename)
        replace_mysql(tablename,value)